package dao;

import com.sun.org.apache.xpath.internal.operations.Or;
import tables.*;

import java.sql.ResultSet;
import java.util.ArrayList;

public class DAO {

    /**
     * 保存用户信息，用于注册
     * @param user
     * @return 返回用户注册后数据库自动生成的id
     */
    public int saveUser(User user) {
        int result = 0;
        String sql = "insert into users (user_account,user_password,user_name,user_age,user_sex,user_address,user_contact)" +
                " values (?,?,?,?,?,?,?)";
        String sql1 = "select * from users where user_account=?";
        try {
             DBUtil.update(sql,user.getUser_account(),user.getUser_password(),user.getUser_name(),user.getUser_age(),user.getUser_sex(),
                    user.getUser_address(),user.getUser_contact());
             ResultSet rs = (ResultSet) DBUtil.query(sql1,user.getUser_account());
             while (rs.next()) result = rs.getInt("user_id");
        } catch (Exception e){
            e.printStackTrace();
        }
        return result;
    }

    /**
     * 修改用户信息
     * @param user
     * @return
     */
    public int updateCustomer(User user){
        int result = 0;
        String sql = "update users set user_account=?,user_password=?,user_name=?,user_age=?,user_sex=?,user_address=?,user_contact=? where user_id=?";
        try{
            result = DBUtil.update(sql,user.getUser_account(),user.getUser_password(),user.getUser_name(),user.getUser_age(),
                    user.getUser_sex(),user.getUser_address(),user.getUser_contact(), user.getUser_id());
        } catch (Exception e){
            e.printStackTrace();
        }
        return result;
    }

    /**
     * 在数据库中查询所有商品的信息并封装成Commodity对象集合返回
     * @return
     */
    public ArrayList<Commodity> getAllGoods(){
        ArrayList<Commodity> goods = new ArrayList<>();
        String sql = "select * from commodities";
        try {
            ResultSet rs = null;
            rs = DBUtil.query(sql);
            while (rs.next()){
                Commodity commodity = new Commodity(
                        rs.getInt("commodity_id"),
                        rs.getDouble("commodity_price"),
                        rs.getString("commodity_name"),
                        rs.getString("commodity_brand"),
                        rs.getInt("commodity_amount"),
                        rs.getString("commodity_type"));
                goods.add(commodity);
            }
        } catch (Exception e){
            e.printStackTrace();
        }
        return goods;
    }

    /**
     * 根据名称查找商品信息
     * @param name
     * @return
     */
    public ArrayList<Commodity> findByName(String name){
        ArrayList<Commodity> foundGoods = new ArrayList<>();
        String sql = "select * from commodities where commodity_name = ? ";
        try {
            ResultSet rs = null;
            rs = DBUtil.query(sql, name);
            while(rs.next()){
                Commodity commodity = new Commodity(
                        rs.getInt("commodity_id"),
                        rs.getDouble("commodity_price"),
                        rs.getString("commodity_name"),
                        rs.getString("commodity_brand"),
                        rs.getInt("commodity_amount"),
                        rs.getString("commodity_type"));
                foundGoods.add(commodity);
            }
        } catch (Exception e){
            e.printStackTrace();
        }
        return foundGoods;
    }

    /**
     * 获取所有订单的信息
     * @return
     */
    public ArrayList<Order> getAllOrders() {
        ArrayList<Order> orders = new ArrayList<>();
        String sql = "select * from orders";
        try{
            ResultSet rs = DBUtil.query(sql);
            while(rs.next()){
                Order order = new Order(
                        rs.getInt("order_id"),
                        rs.getDouble("order_amount"),
                        rs.getString("consignee_name"),
                        rs.getString("consignee_contact"),
                        rs.getString("consignee_address")
                );
                orders.add(order);
            }
        } catch (Exception e){
            e.printStackTrace();
        }
        return orders;
    }

    /**
     * 根据给的id查询对应的订单信息
     * @param id
     * @return
     */
    public ArrayList<Order> findOrderById(int id){
        ArrayList<Order> foundOrder = new ArrayList<>();
        String sql = "select * from orders where order_id=?";
        try {
            ResultSet rs = DBUtil.query(sql,id);
            while(rs.next()){
                Order order = new Order(
                        rs.getInt("order_id"),
                        rs.getDouble("order_amount"),
                        rs.getString("consignee_name"),
                        rs.getString("consignee_contact"),
                        rs.getString("consignee_address")
                );
                foundOrder.add(order);
            }
        } catch (Exception e){
            e.printStackTrace();
        }
        return foundOrder;
    }

    /**
     * 给用户创建一个购物车表格来存储数据，传入的参数为user.name+user.id
     * @param tableName user.name+user.id 用来绑定购物车表和用户
     * @return
     */
    public int createABasket(String tableName){
        int result = 0;
        String sql = "create table ?\n" +
                "(\n" +
                "   goods_id             int not null,\n" +
                "   goods_name             varchar(30),\n" +
                "   goods_amount           int,\n" +
                "   goods_total            double,\n" +
                "   goods_price            double,\n" +
                "   primary key (goods_id)\n" +
                ")";
        try {
            result = DBUtil.update(sql,tableName);
        } catch (Exception e){
            e.printStackTrace();
        }

        return result;
    }

    /**
     * 返回购物车中所有商品的id值
     * @return
     */
    public ArrayList<Integer> getIdsOfBasket() {
        ArrayList<Integer> result = new ArrayList<>();
        String sql = "select * from baskets";
        try {
            ResultSet rs = DBUtil.query(sql);
            while(rs.next()) {
                result.add(rs.getInt("goods_id"));
                System.out.println(rs.getInt("goods_id"));
            }
        } catch (Exception e){
            e.printStackTrace();
        }
        return result;
    }

    /**
     *获取一个用户购物车内所有商品信息
     * @param tableName
     * @return
     */
    public ArrayList<Basket> getAllBaskets(String tableName){
        ArrayList<Basket> sg = new ArrayList<>();
        String sql = "select * from ";
        String str = tableName;
        sql = sql + str;
        try {
            ResultSet rs = DBUtil.query(sql);
            while(rs.next()){
                Basket basket = new Basket(
                        rs.getInt("goods_id"),
                        rs.getString("goods_name"),
                        rs.getInt("goods_amount"),
                        rs.getDouble("goods_total"),
                        rs.getDouble("goods_price")
                );
                sg.add(basket);
            }
        } catch (Exception e){
            e.printStackTrace();
        }
        return sg;
    }

    /**
     * 增加购物车内商品数量 ，返回int
     * @param id
     * @param tableName
     * @return
     */
    public int addAmountOfBasketGoods(int id,String tableName){
        int result = 0;
        String sql0 = "select * from commodities where commodity_id = ?";
        String sql1 = "update commodities set commodity_amount = commodity_amount-1  where commodity_id=?";    //商品总数减1
        String sql2 = "update baskets set goods_amount = goods_amount+1  where goods_id=?";               //购物车中上皮数加1
        String sql5 = "update baskets set goods_total = (goods_amount*goods_price)";   //刷新商品总价格
        try{
            DBUtil.update(sql1,id);
            String sql3 = "select * from baskets where goods_id = ?";
            ResultSet rs1 = DBUtil.query(sql3,id);
            boolean isExist = false;
            while(rs1.next()){
                isExist = true;
            }
            if(isExist) {
                result += DBUtil.update(sql2,id);
                result += DBUtil.update(sql5);
            } else {
                String sql4 = "insert into baskets (goods_id,goods_name,goods_amount,goods_total,goods_price) values (?,?,?,?,?)";
                ResultSet rs2 = DBUtil.query(sql0,id);
                while(rs2.next()){
                   result = DBUtil.update(sql4,rs2.getInt("commodity_id"),rs2.getString("commodity_name"),1,
                           rs2.getDouble("commodity_price"),rs2.getDouble("commodity_price"));
                    DBUtil.update(sql5);
                }
            }
        } catch (Exception e){
            e.printStackTrace();
        }
        return result;
    }

    /**
     *减少购物车内商品数量
     * @param id
     * @param tableName
     * @return
     */
    public int subAmountOfBasketGoods(int id, String tableName){
        int result = 0;
        String sql1 = "update commodities set commodity_amount = commodity_amount+1  where commodity_id=?";    //商品总数加1
        String sql2 = "update baskets set goods_amount = goods_amount-1  where goods_id=?";               //购物车中商品数减1
        String sql3 = "update baskets set goods_total = (goods_amount*goods_price)";            //刷新商品总价格
        String sql4 = "select * from "+ tableName +" where goods_id=?";           //查询购物车中商品
        String sql5 = "delete from baskets where goods_id=?";
        try{
            result += DBUtil.update(sql1,id);
            result += DBUtil.update(sql2,id);
            result += DBUtil.update(sql3);
            ResultSet rs = DBUtil.query(sql4,id);
            boolean isZero = false;
            while (rs.next()) {
                if(rs.getInt("goods_amount")==0) isZero = true;
            }
            if(isZero){
                result += DBUtil.update(sql5,id);
            }
        } catch (Exception e){
            e.printStackTrace();
        }
        return result;
    }


    /**
     * 结算，将购物车清空并将
     * @param id
     * @return
     */
    public int checkBasket(int id){
        String tableName = "baskets";
        int result = 0;
        double total = 0;
        String sql0 = "select * from " + tableName + " where goods_id=?";
        String sql1 = "insert into sales_goods (goods_id,goods_name,goods_amount,goods_total,goods_price)values (?,?,?,?,?)";
        String sql2 = "delete from " + tableName +" where goods_id = ?";
        try {
            ResultSet rs = DBUtil.query(sql0,id);
            while (rs.next()){
                result = DBUtil.update(sql1,rs.getInt("goods_id"),rs.getString("goods_name"),
                        rs.getInt("goods_amount"),rs.getDouble("goods_total"),
                        rs.getDouble("goods_price"));
            }
            result += DBUtil.update(sql2,id);
        } catch (Exception e){
            e.printStackTrace();
        }
        return result;
    }


    /**
     * 创建订单
     * @param userID
     * @return
     */
    public int createOrder(int userID){
        int result = 0;
        String sql0 = "select * from users where user_id = ?";
        String sql1 = "select sum(goods_total) from sales_goods";
        String sql2 = "insert into orders (order_id,order_amount,consignee_name,consignee_contact,consignee_address)" +
                " values (?,?,?,?,?)";
        User user = new User();
        try {
            ResultSet rs = DBUtil.query(sql0,userID);
            while (rs.next()){
                user.setUser_id(userID);
                user.setUser_account(rs.getString("user_account"));
                user.setUser_password(rs.getString("user_password"));
                user.setUser_name(rs.getString("user_name"));
                user.setUser_age(rs.getInt("user_age"));
                user.setUser_sex(rs.getString("user_sex"));
                user.setUser_address(rs.getString("user_address"));
                user.setUser_contact(rs.getString("user_contact"));
            }
            rs = DBUtil.query(sql1);
            double total = 0;
            while(rs.next()) total += rs.getDouble("sum(goods_total)");
//            System.out.println(total);
            result = DBUtil.update(sql2,userID,total,user.getUser_name(),user.getUser_contact(),user.getUser_address());
        } catch (Exception e){
            e.printStackTrace();
        }
        return result;
    }

}
